﻿using Mysqlx.Crud;
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.SQLite;
using System.Drawing;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using static System.Windows.Forms.VisualStyles.VisualStyleElement.Window;

namespace vlcplay
{
    public partial class SQLite : Form
    {
        static Inihelper ini_data = new Inihelper(Directory.GetCurrentDirectory() + "/config.ini");
        SQLiteClass sql = new SQLiteClass();
        SQLiteParameter[] sp =
        {
            new SQLiteParameter("@id",DbType.Int32)
        };

        #region 获取文件当前路径7种方法
#if false
        //获取模块的完整路径。
        string path1 = System.Diagnostics.Process.GetCurrentProcess().MainModule.FileName;
        //获取和设置当前目录(该进程从中启动的目录)的完全限定目录
        string path2 = System.Environment.CurrentDirectory;
        //获取应用程序的当前工作目录
        string path3 = System.IO.Directory.GetCurrentDirectory();
        //获取程序的基目录
        string path4 = System.AppDomain.CurrentDomain.BaseDirectory;
        //获取和设置包括该应用程序的目录的名称
        string path5 = System.AppDomain.CurrentDomain.SetupInformation.ApplicationBase;
        //获取启动了应用程序的可执行文件的路径
        string path6 = System.Windows.Forms.Application.StartupPath;
        //获取启动了应用程序的可执行文件的路径及文件名
        string path7 = System.Windows.Forms.Application.ExecutablePath;

        StringBuilder str = new StringBuilder();
        str.AppendLine("System.Diagnostics.Process.GetCurrentProcess().MainModule.FileName:" + path1);
        str.AppendLine("System.Environment.CurrentDirectory:" + path2);
        str.AppendLine("System.IO.Directory.GetCurrentDirectory():" + path3);
        str.AppendLine("System.AppDomain.CurrentDomain.BaseDirectory:" + path4);
        str.AppendLine("System.AppDomain.CurrentDomain.SetupInformation.ApplicationBase:" + path5);
        str.AppendLine("System.Windows.Forms.Application.StartupPath:" + path6);
        str.AppendLine("System.Windows.Forms.Application.ExecutablePath:" + path7);
        string allPath = str.ToString();
#endif
        #endregion
        public SQLite()
        {
            InitializeComponent();
            sp[0].Value = 1;
            info_init_get();
        }

        private void info_init_get()
        {
            // 读取ini
            string stemp = ini_data.ReadValue("sqlite", "cmd_search");
            if (stemp.Length <= 0)
            {
                MessageBox.Show("查询数值为空");
            }
            else
            {
                textBox_search.Text = stemp;
            }
            stemp = ini_data.ReadValue("sqlite", "cmd_add");
            if (stemp.Length <= 0)
            {
                MessageBox.Show("增加数值为空");
            }
            else
            {
                textBox_add.Text = stemp;
            }
            stemp = ini_data.ReadValue("sqlite", "cmd_del");
            if (stemp.Length <= 0)
            {
                MessageBox.Show("删除数值为空");
            }
            else
            {
                textBox_del.Text = stemp;
            }
            stemp = ini_data.ReadValue("sqlite", "cmd_change");
            if (stemp.Length <= 0)
            {
                MessageBox.Show("改变数值为空");
            }
            else
            {
                textBox_change.Text = stemp;
            }
        }
        private void button_test_Click(object sender, EventArgs e)
        {
            DataTable dt = sql.ExecuteQuery("select * from gcstest", sp);//查询
            dataGridView1.DataSource = dt;
            string str =" ";
            str += "\r\n" + System.Diagnostics.Process.GetCurrentProcess().MainModule.FileName;
            //str += "\r\n" + System.Environment.CurrentDirectory;
            //str += "\r\n" + System.IO.Directory.GetCurrentDirectory();
            //str += "\r\n" + System.AppDomain.CurrentDomain.BaseDirectory;
            //str += "\r\n" + System.AppDomain.CurrentDomain.SetupInformation.ApplicationBase;
            //str += "\r\n" + System.Windows.Forms.Application.StartupPath;
            //str += "\r\n" + System.Windows.Forms.Application.ExecutablePath;
            Console.Write(str);
        }

        public static void ShowDataInListView(DataTable dt, ListView lst)
        {
            //lst.Clear();
            //   lst.View=System.Windows.Forms.View.Details;
            lst.AllowColumnReorder = true;//用户可以调整列的位置
            lst.GridLines = true;

            int RowCount, ColCount, i, j;
            DataRow dr = null;

            if (dt == null) return;
            RowCount = dt.Rows.Count;
            ColCount = dt.Columns.Count;
            //添加列标题名
            /*for (i = 0; i < ColCount; i++)
            {
                lst.Columns.Add(dt.Columns[i].Caption.Trim(), lst.Width / ColCount, HorizontalAlignment.Left);
            }*/

            if (RowCount == 0) return;
            for (i = 0; i < RowCount; i++)
            {
                dr = dt.Rows[i];
                lst.Items.Add(dr[0].ToString().Trim());
                for (j = 1; j < ColCount; j++)
                {
                    lst.Items[i].SubItems.Add((string)dr[j].ToString().Trim());
                }
            }
        }
        private void button_search_Click(object sender, EventArgs e)
        {
            if (textBox_search.Text == null)
            {
                MessageBox.Show("参数为空");

            }
            else
            {
                DataTable dt = sql.ExecuteQuery(textBox_search.Text, sp);//查询
                dataGridView1.DataSource = dt;
                ShowDataInListView(dt,listView1);
            }
        }

        private void button_add_Click(object sender, EventArgs e)
        {
            if (textBox_add.Text == null)
            {
                MessageBox.Show("参数为空");

            }
            else
            {
                //int i = sql.ExecuteNonQuery("insert into gcstest values(6,\"小五\",8,\"小学\",\"4镇\",datetime(CURRENT_TIMESTAMP, 'localtime')", sp);
                //因为datetime默认获取的时间是UTC时间，如果要获取本地时间需要加参数datetime(CURRENT_TIMESTAMP, 'localtime');
                int i = sql.ExecuteNonQuery(textBox_add.Text, sp);//增删改 命令执行
            }
        }

        private void button_del_Click(object sender, EventArgs e)
        {
            if (textBox_del.Text == null)
            {
                MessageBox.Show("参数为空");

            }
            else
            {
                int i = sql.ExecuteNonQuery(textBox_del.Text, sp);//增删改 命令执行
            }
        }

        private void button_change_Click(object sender, EventArgs e)
        {
            if (textBox_change.Text == null)
            {
                MessageBox.Show("参数为空");

            }
            else
            {
                int i = sql.ExecuteNonQuery(textBox_change.Text, sp);//增删改 命令执行
            }
        }

        private void button_create_Click(object sender, EventArgs e)
        {
            string cd = System.Environment.CurrentDirectory;
            if (textBox_name.Text == null)
            {
                MessageBox.Show("输入有效文件名");
            }
            else
            {
                cd += "\\" + textBox_name.Text;
                sql.CreateDB(cd);//手动创建表即可，如果使用这个会覆盖里面的表数据，则需要重新创建
            }
        }

        
        private void button_join_Click(object sender, EventArgs e)
        {
            if (textBox_join_name.Text == null)
            {
                MessageBox.Show("输入有效文件名");
            }
            else 
            {
                sql.SetConnectionString(textBox_join_name.Text, null, 3);
                button_add.Enabled = true;
                button_change.Enabled = true;
                button_del.Enabled = true;
                button_search.Enabled = true;
            }
        }

        private void SQLite_FormClosed(object sender, FormClosedEventArgs e)
        {
            // 写入ini
            ini_data.WriteValue("sqlite", "cmd_search", textBox_search.Text);
            ini_data.WriteValue("sqlite", "cmd_add", textBox_add.Text);
            ini_data.WriteValue("sqlite", "cmd_del", textBox_del.Text);
            ini_data.WriteValue("sqlite", "cmd_change", textBox_change.Text);
        }
        private void button_file_Click(object sender, EventArgs e)
        {
                textBox_join_name.Text = string.Empty;

            #region 选择文件夹
#if flase
            FolderBrowserDialog dialog = new FolderBrowserDialog();
            dialog.Description = "请选择文件夹";

            if (dialog.ShowDialog() == DialogResult.OK || dilog.ShowDialog() == DialogResult.Yes)//th.SetApartmentState(ApartmentState.STA);//打开选中文件函数需要 在打开线程之后需要设置线程属性
            {
                string foldPath = dialog.SelectedPath;
                DirectoryInfo theFolder = new DirectoryInfo(foldPath);
                FileInfo[] dirInfo = theFolder.GetFiles();
                //遍历文件夹
                foreach (FileInfo file in dirInfo)
                {
                    MessageBox.Show(file.ToString());
                }
            }
#endif
#endregion
            #region 选择文件
            OpenFileDialog sfd = new OpenFileDialog();
            //默认显示名称
            sfd.FileName = "sqlite.db";
            //设置匹配文件格式，可包含多种
            //sfd.Filter = "Excel(*.xlsx)|*.xlsx";
            sfd.Filter = "数据库文件(*.db)|*.db";

            if (sfd.ShowDialog() == DialogResult.OK)
            {
                textBox_join_name.Text = sfd.FileName;
            }
            else
            {
                return;
            }
            #endregion
        }
    }
}
